﻿// copyright @ 2009 Farida
//Query for Employee with Product OwnerShip using Stored Procedure

using System;
using System.Data;
using MySql.Data.MySqlClient;

class StoredProcEmpMgt
{
    static void Main()
    {
        StoredProcEmpMgt spemp = new StoredProcEmpMgt();

        // Run Stored Procedure 

        spemp.RunStoredProc();

    }

    // Run Stored Procedure

    public void RunStoredProc()
    {
        MySqlConnection conn =null;
        MySqlDataReader rdr  = null;
        
        Console.WriteLine("Query for Employee with Product OwnerShip");
        Console.WriteLine("------------------------------------------");

        try
        {
            // Create and open connection object

            conn = ConnectToDbClass.ConnectDb();

            conn.Open();

           // Create Command Object identifying the Stored Procedure 

           MySqlCommand cmd = new MySqlCommand("spempdate",conn);


           // Set the Command  Object to execute a Stored procedure
           cmd.CommandType = CommandType.StoredProcedure;

           
           

            //Define Parameters Used

           MySqlParameter param = new MySqlParameter();

           System.Console.WriteLine("List of Employees with product ownership for a Particular Year");

           Console.WriteLine("----------------------------------------------------------------------");


           System.Console.WriteLine("Enter the Year ");

           

           int yr = Convert.ToInt32(Console.ReadLine());

           param.ParameterName = "?uyear";
           param.Value = yr;

           cmd.Parameters.Add(param);

          
          // Execute The Command

            rdr = cmd.ExecuteReader();
            //Console.WriteLine();



         // Iterate thru results


            while (rdr.Read())
            {
               
                //get the columns
                int empid = (int)rdr["emp_id"];
                DateTime owndate = (DateTime)(rdr[1]);
                int prod_id = (int)rdr["prod_id"];

               Console.WriteLine();
               Console.WriteLine();
               Console.WriteLine("Employee Id {0} ",empid );
               Console.WriteLine("OwnnerShip Date {0} ",owndate);
               Console.WriteLine("Product Id {0}", prod_id);
               Console.ReadLine();
                
            }
        }

        finally 
        {
            if (conn !=null)
            {
                conn.Close();
            }
            if (rdr !=null)
            {
                rdr.Close();
            }
        }
    }

}














